package Dao;

import DB.DBConnection;
import pojo.MilkTeaProduct;
import pojo.Order;

import java.sql.*;
import java.util.*;

public class OrderDao {

    public List<Order> getOrders() {
        List<Order> orders = new ArrayList<>();
        String orderSql = "SELECT * FROM order_management";
        String orderItemSql = "SELECT oi.*, mt.product_name, mt.price " +
                "FROM order_items oi " +
                "JOIN milk_tea mt ON oi.product_id = mt.product_id " +
                "WHERE oi.order_id = ?";

        try (Connection connection = DBConnection.getConnection();
             PreparedStatement orderStatement = connection.prepareStatement(orderSql)) {

            ResultSet orderResultSet = orderStatement.executeQuery();

            while (orderResultSet.next()) {
                int orderId = orderResultSet.getInt("order_id");
                String orderStatus = orderResultSet.getString("order_status");
                double totalAmount = orderResultSet.getDouble("total_amount");
                Timestamp createdAt = orderResultSet.getTimestamp("created_at");

                // 获取订单项
                try (PreparedStatement orderItemStatement = connection.prepareStatement(orderItemSql)) {
                    orderItemStatement.setInt(1, orderId);
                    ResultSet orderItemResultSet = orderItemStatement.executeQuery();

                    List<MilkTeaProduct> products = new ArrayList<>();
                    int[] quantities = new int[0];

                    while (orderItemResultSet.next()) {
                        int productId = orderItemResultSet.getInt("product_id");
                        int quantity = orderItemResultSet.getInt("quantity");
                        double unitPrice = orderItemResultSet.getDouble("unit_price");
                        String productName = orderItemResultSet.getString("product_name");


                        MilkTeaProduct product = new MilkTeaProduct(productId, productName, unitPrice, 0, null);
                        products.add(product);

                        quantities = Arrays.copyOf(quantities, quantities.length + 1);
                        quantities[quantities.length - 1] = quantity;
                    }

                    Order order = new Order(orderId, products, quantities, totalAmount, orderStatus,createdAt);
                    orders.add(order);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return orders;
    }

    public List<Order> getOrdersStatus() {
        List<Order> orders = new ArrayList<>();
        String orderSql = "SELECT * FROM order_management where order_status='未完成'";
        String orderItemSql = "SELECT oi.*, mt.product_name, mt.price " +
                "FROM order_items oi " +
                "JOIN milk_tea mt ON oi.product_id = mt.product_id " +
                "WHERE oi.order_id = ?";

        try (Connection connection = DBConnection.getConnection();
             PreparedStatement orderStatement = connection.prepareStatement(orderSql)) {

            ResultSet orderResultSet = orderStatement.executeQuery();

            while (orderResultSet.next()) {
                int orderId = orderResultSet.getInt("order_id");
                String orderStatus = orderResultSet.getString("order_status");
                double totalAmount = orderResultSet.getDouble("total_amount");
                Timestamp createdAt = orderResultSet.getTimestamp("created_at");

                // 获取订单项
                try (PreparedStatement orderItemStatement = connection.prepareStatement(orderItemSql)) {
                    orderItemStatement.setInt(1, orderId);
                    ResultSet orderItemResultSet = orderItemStatement.executeQuery();

                    List<MilkTeaProduct> products = new ArrayList<>();
                    int[] quantities = new int[0];

                    while (orderItemResultSet.next()) {
                        int productId = orderItemResultSet.getInt("product_id");
                        int quantity = orderItemResultSet.getInt("quantity");
                        double unitPrice = orderItemResultSet.getDouble("unit_price");
                        String productName = orderItemResultSet.getString("product_name");

                        MilkTeaProduct product = new MilkTeaProduct(productId, productName, unitPrice, 0, null);
                        products.add(product);

                        quantities = Arrays.copyOf(quantities, quantities.length + 1);
                        quantities[quantities.length - 1] = quantity;
                    }

                    Order order = new Order(orderId, products, quantities, totalAmount, orderStatus,createdAt);
                    orders.add(order);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return orders;
    }

    public int generateOrder(List<MilkTeaProduct> products, int[] quantities) {
        String insertOrderSql = " INSERT INTO order_management (order_status, total_amount) VALUES (?, ?)";
        String insertOrderItemSql = "INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (?, ?, ?, ?)";
        String updateStockSql = "UPDATE milk_tea SET stock = stock - ? WHERE product_id = ?";

        Connection connection = null;
        PreparedStatement orderStatement = null;
        PreparedStatement orderItemStatement = null;
        PreparedStatement stockStatement = null;

        try {
            connection = DBConnection.getConnection();
            connection.setAutoCommit(false);

            // 插入订单信息
            orderStatement = connection.prepareStatement(insertOrderSql, Statement.RETURN_GENERATED_KEYS);
            orderStatement.setString(1, "未完成");
            double totalAmount = 0.0;
            for (int i = 0; i < products.size(); i++) {
                totalAmount += products.get(i).getPrice() * quantities[i];
            }
            orderStatement.setDouble(2, totalAmount);
            orderStatement.executeUpdate();

            // 获取生成的订单ID
            ResultSet generatedKeys = orderStatement.getGeneratedKeys();
            int orderId=0;
            if (generatedKeys.next()) {
                orderId = generatedKeys.getInt(1);

                // 插入订单项信息
                orderItemStatement = connection.prepareStatement(insertOrderItemSql);
                for (int i = 0; i < products.size(); i++) {
                    MilkTeaProduct product = products.get(i);
                    int quantity = quantities[i];

                    orderItemStatement.setInt(1, orderId);
                    orderItemStatement.setInt(2, product.getProduct_id());
                    orderItemStatement.setInt(3, quantity);
                    orderItemStatement.setDouble(4, product.getPrice());
                    orderItemStatement.addBatch();
                }
                orderItemStatement.executeBatch();

                // 减少库存
                stockStatement = connection.prepareStatement(updateStockSql);
                for (int i = 0; i < products.size(); i++) {
                    MilkTeaProduct product = products.get(i);
                    int quantity = quantities[i];

                    stockStatement.setInt(1, quantity);
                    stockStatement.setInt(2, product.getProduct_id());
                    stockStatement.addBatch();
                }
                stockStatement.executeBatch();
            }

            connection.commit(); // 提交事务
            return orderId;

        } catch (SQLException e) {
            e.printStackTrace();
            if (connection != null) {
                try {
                    connection.rollback(); // 回滚事务
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            return -1;
        } finally {
            try {
                if (orderItemStatement != null) orderItemStatement.close();
                if (orderStatement != null) orderStatement.close();
                if (stockStatement != null) stockStatement.close();
                if (connection != null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public int generateVipOrder(List<MilkTeaProduct> products, int[] quantities) {
        String insertOrderSql = " INSERT INTO order_management (order_status, total_amount) VALUES (?, ?)";
        String insertOrderItemSql = "INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (?, ?, ?, ?)";
        String updateStockSql = "UPDATE milk_tea SET stock = stock - ? WHERE product_id = ?";

        Connection connection = null;
        PreparedStatement orderStatement = null;
        PreparedStatement orderItemStatement = null;
        PreparedStatement stockStatement = null;

        try {
            connection = DBConnection.getConnection();
            connection.setAutoCommit(false);

            // 插入订单信息
            orderStatement = connection.prepareStatement(insertOrderSql, Statement.RETURN_GENERATED_KEYS);
            orderStatement.setString(1, "未完成");
            double totalAmount = 0.0;
            for (int i = 0; i < products.size(); i++) {
                totalAmount += products.get(i).getPrice() * quantities[i]*0.9;
            }
            orderStatement.setDouble(2, totalAmount);
            orderStatement.executeUpdate();

            // 获取生成的订单ID
            ResultSet generatedKeys = orderStatement.getGeneratedKeys();
            int orderId=0;
            if (generatedKeys.next()) {
                orderId = generatedKeys.getInt(1);

                // 插入订单项信息
                orderItemStatement = connection.prepareStatement(insertOrderItemSql);
                for (int i = 0; i < products.size(); i++) {
                    MilkTeaProduct product = products.get(i);
                    int quantity = quantities[i];

                    orderItemStatement.setInt(1, orderId);
                    orderItemStatement.setInt(2, product.getProduct_id());
                    orderItemStatement.setInt(3, quantity);
                    orderItemStatement.setDouble(4, product.getPrice());
                    orderItemStatement.addBatch();
                }
                orderItemStatement.executeBatch();

                // 减少库存
                stockStatement = connection.prepareStatement(updateStockSql);
                for (int i = 0; i < products.size(); i++) {
                    MilkTeaProduct product = products.get(i);
                    int quantity = quantities[i];

                    stockStatement.setInt(1, quantity);
                    stockStatement.setInt(2, product.getProduct_id());
                    stockStatement.addBatch();
                }
                stockStatement.executeBatch();
            }

            connection.commit(); // 提交事务
            return orderId;

        } catch (SQLException e) {
            e.printStackTrace();
            if (connection != null) {
                try {
                    connection.rollback(); // 回滚事务
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            return -1;
        } finally {
            try {
                if (orderItemStatement != null) orderItemStatement.close();
                if (orderStatement != null) orderStatement.close();
                if (stockStatement != null) stockStatement.close();
                if (connection != null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }



//    private Order getOrderById(int orderId) {
//        String sql = "SELECT o.order_id, p.product_id, p.product_name, o.quantity, o.total_amount, o.order_status " +
//                "FROM order_management o " +
//                "JOIN milk_tea p ON o.product_id = p.product_id " +
//                "WHERE o.order_id =?";
//        try (Connection connection = DBConnection.getConnection();
//             PreparedStatement statement = connection.prepareStatement(sql)) {
//            statement.setInt(1, orderId);
//            ResultSet resultSet = statement.executeQuery();
//            List<MilkTeaProduct> products = new ArrayList<>();
//            int[] quantities = new int[0];
//            String order_status = null;
//            double total_amount = 0;
//
//            if (resultSet.next()) {
//                int product_id = resultSet.getInt("product_id");
//                int quantity = resultSet.getInt("quantity");
//                String product_name = resultSet.getString("product_name");
//                double price = resultSet.getDouble("price");
//                int stock = resultSet.getInt("stock");
//                products.add(new MilkTeaProduct(product_id, product_name,price,stock,null));
//
//                // 更新数量数组
//                int currentIndex = getProductIndexInOrder(products, product_id);
//                if (currentIndex == -1) {
//                    int[] newQuantities = new int[quantities.length + 1];
//                    System.arraycopy(quantities, 0, newQuantities, 0, quantities.length);
//                    newQuantities[newQuantities.length - 1] = quantity;
//                    quantities = newQuantities;
//                } else {
//                    quantities[currentIndex] += quantity;
//                }
//
//                order_status = resultSet.getString("order_status");
//                total_amount = resultSet.getDouble("total_amount");
//            }
//
//            return new Order(orderId, products, quantities, total_amount, order_status);
//        } catch (SQLException e) {
//            e.printStackTrace();
//            return null;
//        }
//    }

    public boolean updateOrderStatus(int orderId) {
        String sql = "UPDATE order_management SET order_status = '已完成' WHERE order_id = ?";
        try (Connection connection = DBConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setInt(1, orderId);
            int rowsAffected = statement.executeUpdate();
            return rowsAffected > 0; // 返回受影响行数是否大于0，判断更新是否成功
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }




}